{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pymysql\n",
    "from sklearn import (metrics, linear_model)\n",
    "from sklearn.model_selection import train_test_split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to PIC\n",
    "con = pymysql.connect(host='localhost',\n",
    "                             user='user',\n",
    "                             password='password',\n",
    "                             db='pic',\n",
    "                             cursorclass=pymysql.cursors.DictCursor)\n",
    "cur=con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   subject_id  hadm_id  oper_id  item_no         monitortime itemid  value  \\\n",
      "0        4531   104527        1        9 2073-09-01 14:50:00    SV1    148   \n",
      "1        4531   104527        1       10 2073-09-01 14:55:00    SV1    145   \n",
      "2        4531   104527        1       12 2073-09-01 15:05:00    SV1    150   \n",
      "3        4531   104527        1       13 2073-09-01 15:05:00    SV1    147   \n",
      "4        4846   104782        1       15 2109-11-01 16:50:00    SV1    137   \n",
      "\n",
      "            admittime           dischtime  hospital_expire_flag  drop_flag  \n",
      "0 2073-08-28 14:26:27 2073-09-29 12:02:00                     0          0  \n",
      "1 2073-08-28 14:26:27 2073-09-29 12:02:00                     0          0  \n",
      "2 2073-08-28 14:26:27 2073-09-29 12:02:00                     0          0  \n",
      "3 2073-08-28 14:26:27 2073-09-29 12:02:00                     0          0  \n",
      "4 2109-10-17 14:13:19 2109-11-15 10:24:00                     0          0  \n"
     ]
    }
   ],
   "source": [
    "query = \\\n",
    "\"\"\"\n",
    "WITH surgerybp as (\n",
    "SELECT svs.subject_id, svs.hadm_id, svs.oper_id, \n",
    "    svs.item_no,svs.monitortime,svs.itemid,svs.value,\n",
    "    a.admittime,a.dischtime,a.hospital_expire_flag,\n",
    "    CASE WHEN svs.monitortime IS NOT NULL \n",
    "        AND svs.monitortime <= a.dischtime\n",
    "        AND svs.monitortime >= a.admittime THEN 0 \n",
    "        ELSE 1 END AS drop_flag\n",
    "FROM surgery_vital_signs svs\n",
    "LEFT JOIN admissions a\n",
    "ON svs.hadm_id = a.hadm_id\n",
    ")\n",
    "SELECT *\n",
    "FROM surgerybp\n",
    "where drop_flag=0;\n",
    "\"\"\"\n",
    "\n",
    "query_output = pd.read_sql_query(query,con)\n",
    "print(query_output.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        hadm_id  oper_id  hospital_expire_flag\n",
      "0        104527        1                     0\n",
      "737597   105811        1                     0\n",
      "737614   106006        1                     0\n",
      "737620   105999        1                     0\n",
      "737625   105998        1                     0\n"
     ]
    }
   ],
   "source": [
    "surgery_id=query_output.sort_values('oper_id').drop_duplicates(subset=['hadm_id'], keep='first')[['hadm_id','oper_id','hospital_expire_flag']]\n",
    "print(surgery_id.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\software\\Anaconda\\lib\\site-packages\\ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n",
      "E:\\software\\Anaconda\\lib\\site-packages\\ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "#blood pressure variability\n",
    "def SBPvariability(x,y):\n",
    "    vital_BP=query_output[(query_output.hadm_id==x)&(query_output.oper_id==y)&(query_output.itemid=='SV3')]\n",
    "    vital_BP.drop_duplicates(subset=['monitortime'],inplace=True)\n",
    "    vital_BP.sort_values(\"item_no\",inplace=True)\n",
    "    vital_BP.reset_index(drop=True,inplace=True)\n",
    "    n=vital_BP.shape[0]-1\n",
    "    slope=0\n",
    "    if n>3:\n",
    "        global s\n",
    "        s=0\n",
    "        for i in range(0,n):\n",
    "            t=(vital_BP.loc[i+1,'monitortime']-vital_BP.loc[i,'monitortime']).total_seconds()/60\n",
    "            s=abs(vital_BP.loc[i+1,'value']-vital_BP.loc[i,'value'])/t\n",
    "            slope=slope+s\n",
    "        slope=slope/(n+1)\n",
    "        std = np.array(vital_BP.value).std()\n",
    "        mean= np.array(vital_BP.value).mean()\n",
    "    else:\n",
    "        slope=np.nan\n",
    "        std=np.nan\n",
    "        mean=np.nan\n",
    "    return slope,std,mean\n",
    "surgery_id['SBPVariability']=list(map(lambda x,y:SBPvariability(x,y),surgery_id['hadm_id'],surgery_id['oper_id']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "surgery_id['Slope']=surgery_id['SBPVariability'].astype(str).str.split(',').str[0]\n",
    "surgery_id['Std']=surgery_id['SBPVariability'].astype(str).str.split(',').str[1]\n",
    "surgery_id['Mean']=surgery_id['SBPVariability'].astype(str).str.split(',').str[2]\n",
    "surgery_id['Slope']=surgery_id['Slope'].apply(lambda x:x[1:])\n",
    "surgery_id['Mean']=surgery_id['Mean'].apply(lambda x:x[:len(x)-1])\n",
    "surgery_id=surgery_id[surgery_id['Std']!=' nan']\n",
    "for c in ['Slope','Std','Mean']:\n",
    "    surgery_id[c]=surgery_id[c].apply(lambda x:round(float(x),2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Accuracy: 0.9805499664654594\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\software\\Anaconda\\lib\\site-packages\\sklearn\\linear_model\\logistic.py:433: FutureWarning: Default solver will be changed to 'lbfgs' in 0.22. Specify a solver to silence this warning.\n",
      "  FutureWarning)\n",
      "E:\\software\\Anaconda\\lib\\site-packages\\sklearn\\utils\\validation.py:761: DataConversionWarning: A column-vector y was passed when a 1d array was expected. Please change the shape of y to (n_samples, ), for example using ravel().\n",
      "  y = column_or_1d(y, warn=True)\n"
     ]
    }
   ],
   "source": [
    "#logistic regression model predict mortality\n",
    "X=surgery_id[['Slope','Std','Mean']]\n",
    "y=surgery_id[['hospital_expire_flag']]\n",
    "a_train, a_test, b_train, b_test = train_test_split(X, y, test_size=0.3, random_state=42)\n",
    "lr=linear_model.LogisticRegression()\n",
    "lr.fit(a_train,b_train)\n",
    "predict_lr=lr.predict(a_test)\n",
    "print('\\nAccuracy: {}'\\\n",
    "    .format( metrics.accuracy_score(b_test, predict_lr)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
